package com.shujia.spark.sql

import org.apache.spark.sql.{DataFrame, SparkSession}

object Demo10RowTOCol {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .master("local")
      .appName("hive")
      .config("spark.sql.shuffle.partitions", 1)
      .getOrCreate()
    import spark.implicits._
    import org.apache.spark.sql.functions._


    //读取数据
    val scoreDF: DataFrame = spark.read
      .format("csv")
      .option("sep", ",")
      .schema("name STRING,item STRING,score DOUBLE")
      .load("data/sco.txt")

    scoreDF.createOrReplaceTempView("score")

    spark.sql(
      """
        |select
        |name,
        |sum(case when item='数学' then score else 0 end) as math,
        |sum(case when item='英语' then score else 0 end) as english
        |from score
        |group by name
        |
        |""".stripMargin)
    // .show()


    //多行转一行
    val table2DF: DataFrame = scoreDF
      .groupBy($"name")
      .agg(
        sum(when($"item" === "数学", $"score").otherwise(0)) as "math",
        sum(when($"item" === "英语", $"score").otherwise(0)) as "english"
      )
    table2DF.show()


    //一行转换成多行
    table2DF
      .select($"name", explode(map(expr("'数学'"), $"math", expr("'英语'"), $"english")) as Array("item", "score"))
      .show()

  }

}
